<?php


namespace app\api\controller;

use app\common\model\SmokeCostRecord;
use app\common\model\SmokeRecord;
use think\Cache;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
class Cost extends AuthBase
{
    public function index(){
        // 将时间戳转换为 DateTime 对象
        $user=Db::name('smoke_cost_record')
            ->where('user_id',$this->userId)
            ->limit(1)
            ->order('id asc')
            ->find();
        $date1 = new \DateTime();
        $date1->setTimestamp($user['create_time']);
        $date2 = new \DateTime();
        $date2->setTimestamp(time());
// 计算两个日期之间的差值
        $interval = $date1->diff($date2);
        if (!$user){
            $data['year']=0;
            $data['month']=0;
            $data['day']=0;
        }else{
            $data['year']=$interval->y;
            $data['month']=$interval->m;
            $data['day']=$interval->d;
        }
        $data['is_vip']=$this->is_vip;
        return $this->show(1,'',$data);
    }
    public function statistics(){
        $type=input('get.type',1);
        if (!$type){
            $type=1;
        }
        $model=SmokeCostRecord::where('user_id',$this->userId)
            ->order('buy_date desc');
        $num=1;
        switch ($type){
            case 1:
                $model=$model->whereMonth('buy_date');
                $date = new \DateTime();
// 获取当前月份
                $month = $date->format('n');
                $year = $date->format('Y');

// 创建 DateTime 对象，设置为指定月份的第一天
                $date = new \DateTime("{$year}-{$month}-01");

// 修改日期为该月的最后一天
                $date->modify('last day of this month');

// 获取该月的天数
                $num = $date->format('d');
                break;
            case 2:

                $model=$model->whereMonth('buy_date','last month');
                $currentDate = new \DateTime();
// 修改日期为上个月
                $lastMonthDate = clone $currentDate;
                $lastMonthDate->modify('first day of previous month');
// 获取上个月的年份和月份
//                $lastMonthYear = $lastMonthDate->format('Y');
//                $lastMonth = $lastMonthDate->format('n');
// 计算上个月的天数
//                $date = new DateTime();
// 将日期设置为上个月的最后一天
                $currentDate->modify('last day of previous month');
// 获取上个月的天数
                $num = $currentDate->format('d');
//                $num = cal_days_in_month(CAL_GREGORIAN, $lastMonth, $lastMonthYear);
                break;
            case 3:
                $currentYear = date('Y');
                $num=getDaysInYear($currentYear);
                $model=$model->whereYear('buy_date');
                break;
            case 4:
                $currentYear=date('Y', strtotime('last year'));
                $num=getDaysInYear($currentYear);
                $model=$model->whereYear('buy_date','last year');
                break;
            default:
                $res=Db::name('smoke_cost_record')
                    ->where('user_id',$this->userId)
                    ->order('buy_date asc')
                    ->limit(1)
                    ->find();
                if ($res){
                    $num=get_diff_day($res['buy_date'],time());
                }else{
                    $num=0;
                }
                break;
        }
        $price=$model->sum('price');
        if ($price==0){
            $avg_cost=0;
        }else{
            $avg_cost=round($price/$num,2);
        }
        $data['avg_cost']=$avg_cost;
        $data['sum_price']=$price;
        return $this->show(1,'success',$data);
    }

    /**
     * @return \think\response\Json
     * 柱状图
     */
    public function bar_statistics(){
        $type=input('get.type',1);
        if (!$type){
            $type=1;
        }
        if ($type==1){
            //获取近6日日期
            $currentDate = date('Y-m-d');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m-d', strtotime("-$i days", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one);
                $result[$k]['num']=Db::name('smoke_cost_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereDay('buy_date',$v)
                    ->sum('price');
            }
        }elseif($type==2){
            $currentDate = date('Y-m');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y-m', strtotime("-$i month", strtotime($currentDate)));
                $dates[] = $date;
            }

            $result=[];
            foreach ($dates as $k=>$v){
                $one=explode('-',$v);
                $result[$k]['date']=end($one).'月';
                $result[$k]['num']=Db::name('smoke_cost_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereMonth('buy_date',$v)
                    ->sum('price');
            }
        }elseif($type==3){
            $currentDate = date('Y');
            $dates = [];
// 循环获取近 12 个日期
            for ($i = 0; $i < 7; $i++) {
                // 计算当前循环对应的日期
                $date = date('Y', strtotime("-$i year", strtotime($currentDate)));
                $dates[] = $date;
            }
            $result=[];
            foreach ($dates as $k=>$v){
                $result[$k]['date']=$v;
                $result[$k]['num']=Db::name('smoke_cost_record')
                    ->where(['user_id'=>$this->userId])
                    ->whereYear('buy_date',$v)
                    ->sum('price');
            }
        }
        $data['result']=array_reverse($result);;
        $data['is_vip']=$this->is_vip;
//        $data['y_max'] = max(array_column($result, 'num'));
        return show(1,'success',$data);
    }
    public function brand_statistics(){
        $type=input('get.type',1);
        if (!$type){
            $type=1;
        }
        $model=SmokeCostRecord::where('user_id',$this->userId);
        switch ($type) {
            case 1:
                $model=$model->whereMonth('buy_date');
                break;
            case 2:
                $model=$model->whereMonth('buy_date','last month');
                break;
            case 3:
                $model=$model->whereYear('buy_date');
                break;
            case 4:
                $model=$model->whereYear('buy_date','last year');
                break;
            default:
                break;
        }
        $model=$model->field('smoke_id, SUM(price) as total_price')
            ->group('smoke_id')
            ->order('total_price DESC')
            ->limit(5)
            ->select()
            ->toArray();
        foreach ($model as $k=>$v){
            $model[$k]['total_price']=round($v['total_price'],2);
            $str=Db::name('smoke')
                ->where('id',$v['smoke_id'])
                ->value('name');
            $model[$k]['smoke_name']=$str;
//            if (mb_strlen($str, 'UTF-8') > 5) {
//                // 使用 mb_substr 函数截取前五个字符
//                $model[$k]['smoke_name'] = mb_substr($str, 0, 5, 'UTF-8') . '.';
//            } else {
//                $model[$k]['smoke_name'] = $str;
//            }
        }
        if (count($model)<5){
            $arr=array_column($model,'total_price');
            $sum_price=array_sum($arr);
        }else{
            $arr=array_column($model,'total_price');
            $sum_price=array_sum($arr);
            $ids=array_column($model,'smoke_id');
            $other_price=SmokeCostRecord::where('user_id',$this->userId);
            switch ($type) {
                case 1:
                    $other_price=$other_price->whereMonth('buy_date');
                    break;
                case 2:
                    $other_price=$other_price->whereMonth('buy_date','last month');
                    break;
                case 3:
                    $other_price=$other_price->whereYear('buy_date');
                    break;
                case 4:
                    $other_price=$other_price->whereYear('buy_date','last year');
                    break;
                default:
                    break;
            }
            $other_price=$other_price
                ->whereNotIn('smoke_id',$ids)
                ->sum('price');
            $sum_price=$other_price+$sum_price;
            if ($other_price>0){
                $model[5]['smoke_id']=0;
                $model[5]['smoke_name']='其他';
                $model[5]['total_price']=intval($other_price);
            }
        }
        $result['model']=$model;
        $result['sum_price']=$sum_price;
        $result['is_vip']=$this->is_vip;
        return show(1,'success',$result);
    }
    public function log(){
        $type = input('get.type');
        $page = input('param.page', 1, 'intval');
        $limit = input('param.limit', 50, 'intval');
        $where=[];
        $where[] = [
            'user_id', '=',  $this->userId
        ];
        $book = SmokeCostRecord::where($where)
            ->page($page, $limit)
            ->order('buy_date desc');
        $count = SmokeCostRecord::where($where);
        if ($type){
            $num=$book->whereYear('buy_date',$type)->sum('price');
            $book=$book->whereYear('buy_date',$type)
                ->alias('c')
                ->leftJoin('smoke s','s.id=c.smoke_id')
                ->column('c.id,s.name,c.price,c.buy_date');
            $count=$count->whereYear('buy_date',$type)
                ->count();
            $month=12;
            $days=getDaysInYear($type);
        }else{
            $num=$book->sum('price');
            $book = $book->alias('c')
                ->leftJoin('smoke s','s.id=c.smoke_id')
                ->column('c.id,s.name,c.price,c.buy_date');
            $count = $count->count();
            //找出多少天和多少月
            //第一天和今天差了多少天
            $first_smoke=Db::name('smoke_cost_record')
                ->where('user_id',$this->userId)
                ->order('uy_date asc')
                ->limit(1)
                ->value('buy_date');
            if (!$first_smoke){
                $days=1;
                $month=1;
            }else{
                $days=get_diff_day($first_smoke,time());
                $month=ceil($days/30);
            }
        }
        foreach ($book as $k => $v) {
            $book[$k]['buy_date']=date("Y/m/d",$v['buy_date']);
        }
        //处理头部数据
        $top['sum']=$num;
        $top['day_num']=round($num/$days,2);
        $top['month_num']=round($num/$month,2);
        $count = intval(ceil($count / $limit));
        $data['list'] = $book;
        $data['top'] = $top;
        $data['count'] = $count;
        return $this->show(1, '', $data);
    }
    public function log_export(){
        $user=Db::name('user')
            ->where('id',$this->userId)
            ->find();
        $email=input('get.email');
        $type=input('get.type');
        if ($type){
            $res=Db::name('smoke_cost_record')
                ->alias('c')
                ->leftJoin('smoke s','s.id=c.smoke_id')
                ->where('c.user_id',$this->userId)
                ->whereYear('c.buy_date',$type)
                ->order('buy_date desc')
                ->column('c.buy_date,c.price,c.create_time,s.name,c.num');
        }else{
            $res=Db::name('smoke_cost_record')
                ->alias('c')
                ->leftJoin('smoke s','s.id=c.smoke_id')
                ->where('c.user_id',$this->userId)
                ->order('buy_date desc')
                ->column('c.buy_date,c.price,c.create_time,s.name,c.num');
        }
        $spreadsheet = new Spreadsheet();
        // 获取当前活动的工作表（默认第一个工作表）
        $sheet = $spreadsheet->getActiveSheet();

        // 设置表头
        $sheet->setCellValue('A1', '日期');
        $sheet->setCellValue('B1', '花费');
        $sheet->setCellValue('C1', '香烟');
        $sheet->setCellValue('D1', '记录时间');


        // 模拟一些数据，实际应用中可从数据库等获取
        $data_two=[];
        foreach ($res as $k=>$v) {
            $data_two[$k][]=date("Y-m-d H:i",$v['buy_date']);
            $data_two[$k][]=$v['price'];
            $data_two[$k][]=$v['num'];
            $data_two[$k][]=date("Y-m-d H:i",$v['create_time']);
        }
        // 填充数据行
        $row = 2;
        foreach ($data_two as $rowData) {
            $col = 'A';
            foreach ($rowData as $value) {
                $sheet->setCellValue($col. $row, $value);
                $col++;
            }
            $row++;
        }

        // 创建一个临时文件用于保存Excel内容，这里以xlsx格式为例
        $fileName = 'export/'.$user['invite_code'].'.xlsx';
        $writer = new Xlsx($spreadsheet);
        $writer->save($fileName);
        //存储完之后,发送邮件
        $subject=$user['nickname']."您的数据导出已完成";
        $res=sendMailFile($email,$user['nickname'],$fileName,$subject);
        if ($res){
            return $this->show(1,'导出成功请查看邮箱');
        }
        return show(0,'导出失败');
    }
}
